import mysql.connector

# 替换为你的数据库连接信息
config = {
    'user': 'root',
    'password': 'kyds2333',
    'host': 'localhost',
    'database': 'pythonfinal'
}
conn = mysql.connector.connect(**config)
cursor = conn.cursor()

#增加user
def add_user(user_id, username, password, role_id):
    add_user_query = "INSERT INTO users (UserID, Username, Password, RoleID) VALUES (%s, %s, %s, %s)"
    user_data = (user_id, username, password, role_id)
    cursor.execute(add_user_query, user_data)
    conn.commit()
    print(f"Added user: {username}")

#调用函数并传入合适的参数
add_user('s166', '陈雪是', 'wsdsb', '1')

#查找user
def get_user_by_username(username):
    get_user_query = "SELECT * FROM users WHERE Username = %s"
    cursor.execute(get_user_query, (username,))
    result = cursor.fetchall()  # 获取所有匹配的结果
    print("", result)  # 打印查询结果
    if not result:  # 如果没有匹配的结果
        return None
    elif len(result) == 1:  # 如果只有一个匹配结果
        return result[0]  # 返回该结果
    else:  # 如果有多个匹配结果
        print("Warning: Multiple users found with the same username.")
        # 可以根据需要处理多个匹配结果，这里只打印警告信息
        # 返回其中一个结果或者对结果进行进一步处理
        return result[0]  # 这里简单地返回第一个匹配结果

# 调用函数并传入用户名
get_user_by_username('陈雪是')

#删除user
def delete_user(username):
    delete_user_query = "DELETE FROM users WHERE Username = %s"
    cursor.execute(delete_user_query, (username,))
    conn.commit()
    print(f"Deleted user: {username}")
    return True

delete_user('testuser')

def update_user_byuserid(user_id, new_username=None, new_password=None, new_role_id=None):
    if not (new_username or new_password or new_role_id):
        print("No updates specified.")
        return

    update_queries = []
    if new_username:
        # 检查新的用户名是否唯一
        check_query = "SELECT COUNT(*) FROM users WHERE Username = %s"
        cursor.execute(check_query, (new_username,))
        if cursor.fetchone()[0] > 0:
            print("New username already exists. Please choose a different one.")
            return
        else:
            update_queries.append(("UPDATE users SET Username = %s WHERE UserID = %s", new_username))
    if new_password:
        update_queries.append(("UPDATE users SET Password = %s WHERE UserID = %s", new_password))
    if new_role_id:
        update_queries.append(("UPDATE users SET RoleID = %s WHERE UserID = %s", new_role_id))

    for query, value in update_queries:
        cursor.execute(query, (value, user_id))

    conn.commit()
    print(f"Updated user with ID: {user_id}")

update_user_byuserid('s021', new_username='陈雪是')

def update_user_byusername(username, new_username=None, new_password=None, new_role_id=None):
    if not (new_username or new_password or new_role_id):
        print("No updates specified.")
        return
    update_queries = []
    if new_username:
        # 检查新的用户名是否唯一
        check_query = "SELECT COUNT(*) FROM users WHERE Username = %s"
        cursor.execute(check_query, (new_username,))
        if cursor.fetchone()[0] > 0:
            print("New username already exists. Please choose a different one.")
            return
        else:
            update_queries.append(("UPDATE users SET Username = %s WHERE Username = %s", new_username, username))
    if new_password:
        update_queries.append(("UPDATE users SET Password = %s WHERE Username = %s", new_password, username))
    if new_role_id:
        update_queries.append(("UPDATE users SET RoleID = %s WHERE Username = %s", new_role_id, username))

    for query, value1, value2 in update_queries:
        cursor.execute(query, (value1, value2))

    conn.commit()
    print(f"Updated user: {username}")

update_user_byusername('dsb', new_username='s021', new_password='698745')
